﻿using System;
using System.Collections.Generic;

using System.Web;
using System.Data;
using System.Configuration;
using KPIS.DBM;
using KPIS.GERP.ESS.MODEL;

namespace KPIS.GERP.ESS.DAL
{
    public class HrmActivityDAL
    { 
        public DataSet LoadActivity(Nullable<int> Sequence, Nullable<int> JobSiteSeq, Nullable<DateTime> ProjectStartDate, Nullable<DateTime> ProjectEndDate)
        {
            IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
            
            DataSet ds = new DataSet();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);
                dbManager.AddParameters(0, "@Sequence", Sequence);
                dbManager.AddParameters(1, "@ActiveRecord", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(2, "@ProjectStartDate",
                    ProjectStartDate.HasValue ? ProjectStartDate : SystemInfo._DefaultDateStart);
                dbManager.AddParameters(3, "@ProjectEndDate",
                    ProjectEndDate.HasValue ? ProjectEndDate : SystemInfo._DefaultDateEnd);
                dbManager.AddParameters(4, "@JobSiteSeq", JobSiteSeq);

                string sql = "SELECT a.*, at.ACT_TYPE_DESCR, js.JOB_SITE_NAME, ci.CITY_NAME, ci.COUNTRY_SEQ, co.COUNTRY_NAME"
                             + ", CONCAT( 'รอการพิจารณา ') AS APPROVE"
                             +
                             ", (select count(granted) from hrm_ac_activity_requests ar where ar.act_seq = a.act_seq AND granted <> 0 )AS TOTAL"
                             +
                             ", (select count(granted) from hrm_ac_activity_requests ar where ar.act_seq = a.act_seq )AS CANDIDATE"
                             + ", oper.BGM_PROJ_OPER_NAME, oper.ATTEND_COUNT"
                             + " FROM HRM_AC_ACTIVITYS a"
                             + " LEFT JOIN HRM_AC_ACTIVITY_TYPES at ON a.ACT_TYPE_SEQ = at.ACT_TYPE_SEQ"
                             + " LEFT JOIN HRM_MP_JOB_SITES js ON a.JOB_SITE_SEQ = js.JOB_SITE_SEQ"
                             + " LEFT JOIN BGM_PROJECT_OPERATION oper ON a.BGM_PROJ_OPER_SEQ = oper.BGM_PROJ_OPER_SEQ"
                             + " LEFT JOIN MAS_CITIES ci ON a.CITY_SEQ = ci.CITY_SEQ"
                             + " LEFT JOIN MAS_COUNTRIES co ON ci.COUNTRY_SEQ = co.COUNTRY_SEQ"
                             + " WHERE a.RECORD_STATUS = @ActiveRecord"
                             + (Sequence.HasValue ? " AND a.ACT_SEQ = @Sequence " : "")
                             + (JobSiteSeq.HasValue ? " AND a.JOB_SITE_SEQ = @JobSiteSeq" : "")
                             +
                             " AND (a.DATE_ACTIVITY_START >= @ProjectStartDate OR a.DATE_ACTIVITY_END <= @ProjectEndDate )";

                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
            }
            catch
            {
            }
            finally
            {
                dbManager.Dispose();
            }
            return ds;
        }
        public void InsertActivityRequests(int ActivitySequence, int EmpSequence, int CreatedBy)
        {
            IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);

                dbManager.AddParameters(0, "@ActSeqence", ActivitySequence);
                dbManager.AddParameters(1, "@EmpSequence", EmpSequence);
                dbManager.AddParameters(2, "@act_request_status", SystemInfo._ProcessRecordStatus);
                dbManager.AddParameters(3, "@CreatedBy", CreatedBy);
                dbManager.AddParameters(4, "@CreatedWhen", DateTime.Now);

                strSQL = "INSERT INTO HRM_AC_ACTIVITY_REQUESTS"
                    + " (ACT_SEQ, EMP_SEQ, ACT_REQUEST_STATUS, CREATED_BY, CREATED_WHEN)"
                    + " VALUES(@ActSeqence, @EmpSequence, @act_request_status, @CreatedBy, @CreatedWhen)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public void UpdateActivityRequests(Nullable<int> Sequence, int UpdatedBy)
        {
            IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
            
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@Seqence", Sequence);
                dbManager.AddParameters(1, "@act_request_status", SystemInfo._ApproveRecordStatus);
                dbManager.AddParameters(2, "@UpdatedBy", UpdatedBy);
                dbManager.AddParameters(3, "@UpdatedWhen", DateTime.Now);

                strSQL = "UPDATE HRM_AC_ACTIVITY_REQUESTS"
                    + " SET ACT_REQUEST_STATUS = @act_request_status"
                    + ", UPDATED_BY = @UpdatedBy"
                    + ", UPDATED_WHEN = @UpdatedWhen"
                    + " WHERE ACT_REQUEST_SEQ = @Seqence";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
